package hostsystem;

import java.io.IOException;
//import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.mysql.jdbc.Blob;

public class CreateBooking2 extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws IOException, ServletException {
		
		HttpSession session = req.getSession();
		String date = req.getParameter("date");
		String time = req.getParameter("time");

		if (session.getAttribute("islogin") == null) {
			resp.sendRedirect("login.html");
			return;
		}

		
		//System.out.println("createbooking2 " + date + "    " + time);
		Connection con = hostdb.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sqlstm = "";
		List<Staffprofile> staffs = new ArrayList<Staffprofile>();

		try {
			switch (Integer.parseInt(time)) {
			case 1:
				sqlstm = "select staff_id from staff_info where staff_id not in(select staff_id from time_table where date=? and time in(1,3))";
				break;
			case 2:
				sqlstm = "select staff_id from staff_info where staff_id not in(select staff_id from time_table where date=? and time in(2,3))";
				break;
			case 3:
				sqlstm = "select staff_id from staff_info where staff_id not in (select staff_id from time_table where date=? and time between 1 and 3)";
				break;
			}
/*			System.out.println("select * from staff_info where staff_id in ("
					+ sqlstm + ")");*/
			ps = con.prepareStatement("select * from staff_info where staff_id in ("
					+ sqlstm + ")");
			ps.setString(1, date);
			rs = ps.executeQuery();

			while (rs.next()) {
				Staffprofile s = new Staffprofile(rs.getInt("staff_id"),
						rs.getString("name"), rs.getTimestamp("birthday"),
						rs.getInt("sex"), rs.getDouble("wage"),
						rs.getInt("skill_id"), (Blob) rs.getBlob("photo"),
						rs.getString("description"));
				staffs.add(s);
			}

			ps.close();
			con.close();

			session.setAttribute("staffs", staffs);
			req.getRequestDispatcher("/createbooking2.jsp").forward(req, resp);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws IOException {
		resp.sendRedirect("login.html");
	}
}